//出入管理
let db = require("../db/index");
function formatDate(date) {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0'); // 月份是从0开始的，所以需要加1
    const day = String(date.getDate()).padStart(2, '0');
    const hours = String(date.getHours()).padStart(2, '0');
    const minutes = String(date.getMinutes()).padStart(2, '0');
    return `${year}-${month}-${day} ${hours}:${minutes}`;
}


// 查询所有出入记录
exports.entryExit = (req, res) => {
    var sql =
        'select entryexit.* ,Owner ,Phone,CardNum,Negative from entryexit,room where  room.RoomID=entryexit.RoomID ';
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send(data);
    });
};
//查询所有的访客
exports.visitor = (req, res) => {
    var sql =
        'select visitor.* ,Owner ,Phone,BuildingName,FloorNumber,UnitName,RoomNumber,CardNum from visitor,room,building, unit,floor where building.BuildingID=unit.BuildingID AND unit.UnitID=floor.UnitID and floor.FloorID=room.FloorID and room.RoomID=visitor.RoomID ';
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send(data);
    });
};
//上传图片
exports.updated = (req, res) => {
    console.log(req.file, 999999);
    if (req.file) {
        res.send(req.file)
    } else {
        res.status(400).json({ message: '没有上传文件' });
    }
};
//添加访客
exports.addVisitor = (req, res) => {
    const beginDate = getDate(0, req.body.visitorBegin)
    const endDate = getDate(0, req.body.visitorEnd)
    const newDate = getDate(1)
    var sql =
        `SELECT RoomID from building, unit,floor,room where building.BuildingID=unit.BuildingID AND unit.UnitID=floor.UnitID and floor.FloorID=room.FloorID AND building.BuildingName = '${req.body.build}'and unit.UnitName='${req.body.unit}' and room.RoomNumber='${req.body.room}'; `;
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        const roomID = data[0].RoomID

        var sql =
            `INSERT INTO visitor (RoomID, VisitorImg, VisitorName, VisitrorPhone, VisitorDate, VisitorSource, VisitorBegin, VisitorEnd, VisitorAddMan, VisitorNote,VisitorID) VALUES(${roomID},'${req.body.visitorImg}','${req.body.visitorName}','${req.body.visitorPhone}','${newDate}','物业平台','${beginDate}','${endDate}','${req.body.addMan}','${req.body.visitorNote}','${req.body.visitorID}') `;
        db.query(sql, [], (err, data) => {
            if (err) {
                console.log("出错了", err);
                return res.send("错误" + err.message);
            }
            res.send({
                status: 500
            });


        });

    });
};
//查询所有门禁权限
exports.permissions = (req, res) => {
    var sql =
        'select doorpermissions.* ,Owner ,Phone,BuildingName,FloorNumber,UnitName,RoomNumber,Negative from doorpermissions,room,building, unit,floor where building.BuildingID=unit.BuildingID AND unit.UnitID=floor.UnitID and floor.FloorID=room.FloorID and room.RoomID=doorpermissions.RoomID ';
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send(data);
    });
};
//修改权限
exports.updatePermissions = (req, res) => {

    var sql =
        `update doorpermissions set Permissions='${req.body.permissions}' where ID=${req.body.id}`;
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send({
            status: 500,
        });
    });
};
//卡片管理的卡片状态
exports.updateCardState = (req, res) => {
    console.log(req.body);
    var sql =
        `SELECT RoomID from building, unit,floor,room where building.BuildingID=unit.BuildingID AND unit.UnitID=floor.UnitID and floor.FloorID=room.FloorID and Owner='${req.body.Owner}';`;
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        var sql =
            `update doorpermissions set CardState='${req.body.CardState}',ICNum='${req.body.ICNum}',RoomID=${data[0].RoomID} where ID=${req.body.ID}`;
        db.query(sql, [], (err, data) => {
            if (err) {
                console.log("出错了", err);
                return res.send("错误" + err.message);
            }
            res.send({
                status: 200,
            });
        });
    });


};
//卡片管理的新增
exports.insertCard = (req, res) => {
    const { ICNum, CardState, Owner } = req.body

    // insert into doorpermissions(Permissions,RoomID,ICNum,CardState)values()
    var sql =
        `SELECT RoomID,BuildingName,UnitName from building, unit,floor,room where building.BuildingID=unit.BuildingID AND unit.UnitID=floor.UnitID and floor.FloorID=room.FloorID and Owner='${Owner}';`;
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        const { RoomID, BuildingName, UnitName } = data[0]
        var sql =
            `insert into doorpermissions(Permissions,RoomID,ICNum,CardState)values('${BuildingName + UnitName}',${data[0].RoomID},'${ICNum}','${CardState}');`;
        db.query(sql, [], (err, data) => {
            if (err) {
                console.log("出错了", err);
                return res.send("错误" + err.message);
            }
            res.send({
                status: 200
            })
        });
    });


};
// 查询所有门禁设备
exports.allEquipment = (req, res) => {
    var sql =
        'select * from equipment;';
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send(data);
    });
};
//新增门禁设备
exports.addEquipment = (req, res) => {
    console.log(req.body);
    const currentDate = new Date();
    const now = formatDate(currentDate)
    const { equipmentID, equipmentName, type, area, build, unit, location, simState } = req.body
    let str = ''
    if (location) {
        str = location
    } else {
        str = build + unit
    }
    var sql =
        `insert into equipment(EquipmentName,AccessControl,EquipmentID,EquipmentState,Type,Area,adddate,SIMState)values('${equipmentName}','${str}','${equipmentID}','在线','${type}','${area}','${now}','${simState}')`;
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send({
            status: 200
        });
    });
};
//修改门禁设备
exports.editEquipment = (req, res) => {
    const { equipmentID, equipmentName, type, area, build, unit, location, equipmentState, id, simState } = req.body
    let str = ''
    if (location) {
        str = location
    } else {
        str = build + unit
    }
    var sql =
        `update equipment set  EquipmentName='${equipmentName}',AccessControl='${str}',EquipmentID='${equipmentID}',EquipmentState='${equipmentState}',Type='${type}',Area='${area}',SIMState='${simState}'where ID=${id} ;`;
    db.query(sql, [], (err, data) => {
        if (err) {
            console.log("出错了", err);
            return res.send("错误" + err.message);
        }
        res.send({
            status: 200
        });
    });
};
//批量设置密码
exports.allPassword = (req, res) => {
    const { pass, id } = req.body
    if (id) {
        var sql =
            `update equipment set password='${pass}' where ID=${id} ;`;
        db.query(sql, [], (err, data) => {
            if (err) {
                console.log("出错了", err);
                return res.send("错误" + err.message);
            }
            res.send({
                status: 200
            });
        });

    } else {
        var sql =
            `update equipment set password='${pass}';`;
        db.query(sql, [], (err, data) => {
            if (err) {
                console.log("出错了", err);
                return res.send("错误" + err.message);
            }
            res.send({
                status: 200
            });
        });

    }


};